package com.hnkywl.jip.dao;

import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * 说明：
 * 包:        com.hnkywl.jip.dao
 * 类描述:    收付款
 * 创建人:    ZHUCHG
 * 创建时间: 13-2-20 下午1:10
 * 修改人:    ZHUCHG
 * 修改时间: 13-2-20 下午1:10
 * 修改备注: [说明本次修改内容]
 * 版本:        [v1.0]
 */
@Repository
public class ReceiptPaymentDao extends SimpleHibernateTemplate {

    /**
     * @param type       收款/付款
     * @param startTime  开始时间
     * @param endTime    结束时间
     * @param customerId 客户ID
     * @param billNumber 票据号
     * @param companyId  公司ID
     * @param voucherId
     * @param operator   操作人
     * @return
     */
    public Page<Object[]> findReceiptPayment(Integer type, Date startTime, Date endTime, Integer customerId,
                                             String billNumber, Integer companyId, String voucherId, String operator, Integer status, Integer limit, Integer start) {
        // sql查询字段
        //单据号码 收款日期 收款类型 客户名称 收款金额 票据号码 入帐银行 类型 审核人 公司名称 凭证号
        String sqlColumn = "select t.sfkid," +
                "sfkno=dbo.gf_xxid_view((case when t.type in (11,12,13) then 4 when type in (21,22) then 3 end),t.sfkid)," +
                "t.dt," +
                "typename=(case t.type " +
                " when 11 then '供应付款' " +
                " when 12 then '销售退款' " +
                " when 13 then '费用报支' " +
                " when 21 then '销售收款' " +
                " when 22 then '供应退款' " +
                " else '*未知*' end)," +
                "custname=(dbo.gf_custid_name(t.custid))," +
                "t.je," +
                "incno=(select name from v_cw_inc_type where incid=t.inctype)+(case when t.incid<>'' then '#'+t.incid else '' end)," +
                "bankname=(select shortname from t_cw_bank where bankid=t.bankid)," +
                "stypename=(case t.stype " +
                " when 1 then '应收款' " +
                " when 2 then '预收款' " +
                " when 3 then '应付款' " +
                " when 4 then '预付款' end)," +
                " shr=m.name," +
                " compname=(dbo.gf_compid_shortname(t.compid))," +
                " t.jiuqi_voucher_num," +
                " operation=(case when :status in (1,4,5,9) then 'add' when :status=7 then 'delete' when  :status in (6,8) then 'reset' else 'none' end)" +
                " from t_cw_sfk t,t_crm_man m,t_sys_comp c ";
        //查询记录数
        String sqlCount = "select count(t.sfkid) from t_cw_sfk t,t_crm_man m,t_sys_comp c ";
        //sql查询条件
        String sqlCondition = " where t.compid=c.compid and t.zdr=m.manid and isnull(t.isvirtual,0)=0 and t.inctype>0 " +
                (type != null ? "and (:type=1 and t.type>20 or :type=2 and t.type<20) " : "") +
                "and t.dt between :startTime and :endTime " +
                (customerId != null ? "and t.custid=:customerId " : "") +
                (billNumber != null && !billNumber.isEmpty() ? "and t.sfkno=:billNumber " : "") +
                (companyId != null && companyId != 0 ? "and t.compid=:companyId " : "") +
                (operator != null && !operator.isEmpty() ? "and m.name like :operator" : "") +
                (voucherId != null && !voucherId.isEmpty() ? "and t.jiuqi_voucher_num=:voucherId" : "") +
                " and ((:status in (1,2,3) and t.u8insflag=:status and isnull(t.jiuqi_insert_flag,1)=1) " +
                " or (:status=4 and t.u8delflag=2) " +
                " or (:status=5 and t.u8delflag=3)" +
                " or (:status in(6,7) and t.jiuqi_insert_flag=:status-4)" +
                " or (:status=8 and t.jiuqi_delete_flag=2)" +
                " or (:status=9 and t.jiuqi_delete_flag=3)) ";
        String order = " order by t.dt";
        //获取总记录数
        SQLQuery query = createSQLQuery(sqlCount + sqlCondition);
        if (type != null)
            query.setInteger("type", type);
        query.setDate("startTime", startTime);
        query.setDate("endTime", endTime);
        if (customerId != null)
            query.setInteger("customerId", customerId);
        if (billNumber != null && !billNumber.isEmpty())
            query.setString("billNumber", billNumber);
        if (companyId != null && companyId != 0)
            query.setInteger("companyId", companyId);
        if (operator != null && !operator.isEmpty())
            query.setString("operator", "%" + operator + "%");
        if (voucherId != null && !voucherId.isEmpty())
            query.setString("voucherId", voucherId);
        query.setInteger("status", status);
        Integer total = (Integer) query.uniqueResult();
        List<Object[]> result = null;

        if (total > 0) {
            //获取列表数据
            query = createSQLQuery(sqlColumn + sqlCondition + order);
            if (type != null)
                query.setInteger("type", type);
            query.setDate("startTime", startTime);
            query.setDate("endTime", endTime);
            if (customerId != null)
                query.setInteger("customerId", customerId);
            if (billNumber != null && !billNumber.isEmpty())
                query.setString("billNumber", billNumber);
            if (companyId != null && companyId != 0)
                query.setInteger("companyId", companyId);
            if (operator != null && !operator.isEmpty())
                query.setString("operator", operator);
            if (voucherId != null && !voucherId.isEmpty())
                query.setString("voucherId", voucherId);
            query.setInteger("status", status);
            result = query.setFirstResult(start).setMaxResults(limit).list();
        }
        //返回值
        Page<Object[]> page = new Page<Object[]>();
        page.setTotalCount(total);
        if (result != null && !result.isEmpty())
            page.setResult(result);
        return page;
    }

    /**
     * 收付款凭证主表信息
     *
     * @param id 收付款ID
     */
    public List getVoucherMaster(Integer id) {
        String sql = "select vchrtypecode=(case when t.inctype in(5,6) then '转帐' else '银行' end),c.company_name,c.company_code " +
                " from t_cw_sfk t,t_jiuqi_company c where t.compid=c.company_id and t.sfkid=?";
        Object[] result = (Object[]) createSQLQuery(sql, id).uniqueResult();
        if (result != null)
            return Arrays.asList(result);
        return null;
    }

    public List getVoucher(Integer id) {
        String vchrtypecode = null;
        String sql = "{call sp_jiuqi_voucher_receipt_payment(?,?)}";
        List<Object[]> list = createSQLQuery(sql, id, vchrtypecode).list();
        return list;
    }

    public List getRevokeInfo(Integer id) {
        String sql = "select s.jiuqi_uuid,s.jiuqi_voucher_id,c.company_code,s.jiuqi_acctyear from t_cw_sfk s,t_jiuqi_company c where s.compid=c.company_id and s.sfkid=?";
        Object[] result = (Object[]) createSQLQuery(sql, id).uniqueResult();
        if (result != null)
            return Arrays.asList(result);
        return null;
    }
}
